/****** Object:  StoredProcedure [dbo].[web_insert_voucher]    Script Date: 10/19/2013 09:22:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'web_insert_voucher')
DROP PROCEDURE web_insert_voucher
GO

-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[web_insert_voucher] 
@aparatoid varchar(100),
@apmokejimorusis varchar(100),
@kortelesnr varchar(100),
@kvitonr2 varchar(100),
@data varchar(50),
@znr int
AS
BEGIN
DECLARE @galvaid int
DECLARE @kvitonr int
DECLARE @KvNr int
DECLARE @tmp varchar(11)

SET NOCOUNT ON

BEGIN TRANSACTION
    SELECT top 1 @tmp = SUBSTRING(kvitonr2,1,CHARINDEX('/', kvitonr2)-1) from kvitogalva where aparatoid = @aparatoid and znr = @znr order by id desc


if (@tmp is null)
SET @KvNr = 1
else
SET @KvNr = CAST(@tmp as int) + 1    
SET @kvitonr = (@znr*65536)+@KvNr


insert into Kvitogalva  (aparatoid,apmokejimorusis,kortelesnr,kvitonr,kvitonr2,data,import,year,month,day,hour,minute,znr) 
values (@aparatoid,@apmokejimorusis,@kortelesnr,@kvitonr ,CAST(@KvNr as varchar(20)) + '/' + CAST(@znr as varchar(20)),@data,1,year(@data),month(@data),day(@data), DATEPART(hh, @data),DATEPART(mi,@data),@znr)

SET @galvaid  = SCOPE_IDENTITY()

DECLARE @barkodas varchar(100)
DECLARE @kiekis varchar(100)
DECLARE @kaina varchar(100)
DECLARE @mokesciai varchar(100)
DECLARE @suma decimal(12,2)
DECLARE @mok decimal(12,2)

DECLARE MY_CURSOR CURSOR 
  LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR 
SELECT barkodas,kiekis,kaina,mokesciai
FROM #tt_eilutes

OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @barkodas,@kiekis,@kaina,@mokesciai
WHILE @@FETCH_STATUS = 0
BEGIN 

SET @suma = cast(isnull(@kaina,0) as decimal(12,2))/cast(isnull(@kiekis,1) as decimal(12,2))
SET @mok = cast(isnull(@kaina,0) as decimal(12,2))-(cast(isnull(@kaina,0) as decimal(12,2))*100/(100+cast(@mokesciai as numeric(10,2)))) 

insert into Kvitoeilute (barkodas,preke,kiekis,kaina,mokesciai,galvosid,suma ) 
values (@barkodas,@barkodas,@kiekis,@suma,@mok,@galvaid,cast(isnull(@kaina,0) as decimal(12,2)))

FETCH NEXT FROM MY_CURSOR INTO @barkodas,@kiekis,@kaina,@mokesciai
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

update Kvitogalva set 
suma=(select sum(suma) from Kvitoeilute where galvosid=@galvaid),
mokesciai=(select sum(mokesciai) from Kvitoeilute where galvosid=@galvaid) where id = @galvaid
COMMIT TRANSACTION

END

GO



